<?php

class Manage_DataService_Navigation {

  /**
   * @param  int $nav_id
   * @return array
   */
  public static function getNav($nav_id) {
    $sql = "
      SELECT node.*, COUNT(parent.id) - 1 AS depth
      FROM core_nav_item AS node,
      core_nav_item AS parent
      WHERE (node.lft BETWEEN parent.lft AND parent.rgt) AND (node.nav_id = parent.nav_id) AND (node.nav_id =  ?)
      GROUP BY node.id
      ORDER BY node.lft ASC
      ;";

    $db = Db::factory();
    return $db->fetchAll($sql, $nav_id);
  }

  public function updateDepth($nav_id = NULL) {
    $db = Db::factory();
    $sql = "
      update core_nav_item, (select node.id  as node_id, count(*) -1 as depth from core_nav_item as node, core_nav_item parent where parent.nav_id =  node.nav_id and node.lft BETWEEN parent.lft and parent.rgt ) as table2
      set core_nav_item.depth =  table2.depth
      where core_nav_item.id =  table2.node_id;
      ";
//    $db->query($sql);
  }

  /**
   * data=  {nav_id=>'', label=>'', uri=>''}
   * @param array $data
   */
  public function addNode(array $data, $nav_id, $prev_id = 0, $parent_id = 0) {
    $db = Db::factory();

    $label = $data['label'];
    $uri = $data['uri'];
    $data['nav_id'] = $nav_id;
    $db->beginTransaction();

    $sql = "";
    if ($prev_id) {
      $sql = "select rgt from core_nav_item where nav_id = $nav_id and id =  $prev_id";
    } elseif ($parent_id) {
      $sql = "select rgt-1 from core_nav_item where nav_id = $nav_id and id =  $parent_id";
    } else {
      $sql = "select max(rgt) as max_rgt from core_nav_item where nav_id = $nav_id";
    }
    $rgt = (int) $db->fetchOne($sql);
    if ($rgt) {
      $sql = "update core_nav_item set lft =  lft+ 2 where lft> $rgt";
      $db->query($sql);
      $sql = "update core_nav_item set rgt =  rgt + 2 where rgt > $rgt";
      $db->query($sql);
    }

    try {
      $insert = array(
          'label' => $label,
          'nav_id' => $nav_id,
          'lft' => $rgt + 1,
          'rgt' => $rgt + 2,
          'uri' => $uri,
      );
      $sql = "
        insert into core_nav_item(label,nav_id,lft, rgt,uri)
        values(:label, :nav_id, :lft, :rgt, :uri)";

      $db->query($sql, $insert);
      $db->commit();
      $this->updateDepth();

    } catch (Exception $e) {
      $db->rollBack();
      echo $e->getMessage();
    }
  }

  /**
   *
   * @param  int $id
   * @param  int $parent_id
   * @return int
   */
  public function moveNode($id, $parent_id = 0, $prev_id = 0) {
    $db = Db::factory();
    $sql = "select * from core_nav_item where id  = ?";
    $row = $db->fetchRow($sql, $id);
    $lft1 = (int) $row['lft'];
    $rgt1 = (int) $row['rgt'];
    $nav_id = $row['nav_id'];
    $width = $rgt1 - $lft1 + 1;

    if ($prev_id) {
      $rgt2 = (int) $db->fetchOne("select rgt from core_nav_item where id = ?", (int) $prev_id);
    } else if ($parent_id) {
      $rgt2 = (int) $db->fetchOne("select rgt -1 from core_nav_item where id = ?", (int) $parent_id);
    } else {
      $rgt2 = 0;
    }

    $db->beginTransaction();
    try {
      $sql = "update core_nav_item set lft =  -lft, rgt = -rgt where nav_id =  $nav_id and lft >= $lft1 and rgt <= $rgt1";
      
      $db->query($sql);

      $sql = "
        update core_nav_item set lft = lft+ $width where nav_id = $nav_id and lft > $rgt2;
        ";
      
      $db->query($sql);

      $sql = "
        update core_nav_item set rgt = rgt + $width where nav_id =  $nav_id and rgt > $rgt2;
     ";
      
      $db->query($sql);

      $sql = "
        update core_nav_item set lft = lft- $width where nav_id =  $nav_id and lft > $rgt1;
        ";
      
      $db->query($sql);

      $sql = "
        update core_nav_item set rgt = rgt- $width where nav_id = $nav_id and rgt > $rgt1;
      ";
      
      $db->query($sql);

      /**
       * change internal node
       */
      if ($prev_id) {
        $rgt2 = (int) $db->fetchOne("select rgt from core_nav_item where id = ?", (int) $prev_id);
        $delta = $rgt2 - $rgt1 + 2;
      } else if ($parent_id) {
        $rgt2 = (int) $db->fetchOne("select rgt from core_nav_item where id = ?", (int) $parent_id);
        $delta = $rgt2 - $rgt1 - 1;
      } else {
        $delta = - $rgt1 + 2;
      }

      $sql = "
        update core_nav_item set lft = $delta - lft, rgt = $delta - rgt where lft < 1 and nav_id = $nav_id;
      ";

      
      $db->query($sql);

      $db->commit();
      $this->updateDepth();
    } catch (Exception $e) {
      $db->rollBack();
      echo $e->getMessage();
    }
  }

  /**
   *
   * @param <type> $id 
   */
  public function removeNode($id) {
    $sql = "select * from core_nav_item where id = $id";
    $db = Db::factory();
    $row = $db->fetchRow($sql);
    $nav_id = $row['nav_id'];
    $lft = $row['lft'];
    $rgt = $row['rgt'];
    $width = $rgt - $lft + 1;
    $db->beginTransaction();
    $db->query("delete from core_nav_item where nav_id = $nav_id and lft BETWEEN $lft and $rgt ");
    $db->query("update core_nav_item set lft =  lft -  $width where nav_id =  $nav_id and lft > $lft");
    $db->query("update core_nav_item set rgt =  rgt -  $width where nav_id =  $nav_id and rgt > $lft");
    $db->commit();
  }

  /**
   * @param int $nav_id
   */
  public function clean($nav_id) {
    $db = Db::factory();

    $db->beginTransaction();
    $sql = "delete from core_nav_item where nav_id = ? and lft <> 1;";
    $db->query($sql, (int) $nav_id);

    $sql = "update core_nav_item set rgt = 2 where lft =  1 and nav_id = ?";
    $db->query($sql, $nav_id);

    $db->commit();
  }

  public function getNodeById($id){
    $sql =  "SELECT * from core_nav_item WHERE id = ?";
    return Db::factory()->fetchRow($sql, $id);
  }
}